package service.implement;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Scanner;

import model.Employee;
import service.AbstractConnection;
import service.Constan;
import service.EmployeeAPI;

public class EmployeeDAO extends AbstractConnection implements EmployeeAPI {
	
	public EmployeeDAO() {
		conn = getCurrentConnection();
	}
	@Override
	public int addEmployee(Employee emp) {
		String query="insert into employee values(?,?,?,?)";
		try {
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setInt(1, java.sql.Types.NULL);
			ps.setString(2, emp.getName());
			ps.setString(3, emp.getUsername());
			ps.setString(4, emp.getEmail());
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
		
			e.printStackTrace();
		} catch (Exception e) {
		
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}
	@Override
	public int updateEmployee(Employee emp) {
		String query = "update employee set name=?, username=? , email=? where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, emp.getName());
			ps.setString(2, emp.getUsername());
			ps.setString(3, emp.getEmail());
			ps.setInt(4, emp.getId());
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}

	@Override
	public int deleteEmployee(int id) {
		String query = "delete from employee where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ps.executeUpdate();
			return Constan.SER_STATUS_SUCSESS;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return Constan.SER_STATUS_EROOR;
	}

	@Override
	public Employee getEmployeeById(int id) {
		String query = "SELECT * FROM employee where id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id1 = rs.getInt(1);
				String name = rs.getString(2);
				String username=rs.getString(3);
				String email=rs.getString(4);
				Employee emp=new Employee(id,name,username,email);
				return emp;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return null;
	}

	@Override
	public ArrayList<Employee> getEmployee() {
		ArrayList<Employee> dsEmp = new ArrayList<>();
		try {
			CallableStatement cs=getCurrentConnection().prepareCall("{call display}");
			
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String username = rs.getString(2);
				String password = rs.getString(3);
				String email = rs.getString(4);
				Employee em = new Employee(id, username, password, email);
				dsEmp.add(em);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return dsEmp;
	}

	@Override
	public boolean check(int employeeid) {
		String query = "Select * FROM employee WHERE id=?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setInt(1, employeeid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return false;
	}

	@Override
	public ArrayList<Employee> getEmployeeByName(String name) {
		ArrayList<Employee> list = new ArrayList<>();
		String query = "SELECT * FROM employee where name like ?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, "%" + name + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name1=rs.getString(2);
				String username = rs.getString(3);
				String email = rs.getString(4);
				Employee emp = new Employee(id, name1, username,email);
				list.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			closeConnection();
		}
		return list;
	}
	@Override
	public ArrayList<Employee> getEmployeeByUsername(String username) {
		ArrayList<Employee> list = new ArrayList<>();
		String query = "SELECT * FROM employee where username like ?";
		try {
			PreparedStatement ps = getCurrentConnection().prepareStatement(query);
			ps.setString(1, "%" + username + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name=rs.getString(2);
				String username1 = rs.getString(3);
				String email = rs.getString(4);
				Employee emp = new Employee(id, name, username1,email);
				list.add(emp);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			closeConnection();
		}
		return list;
	}
	
	
}
